﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace BlogsManage
{
    class DBhelp
    {
        /// <summary>
        /// DBHelper帮助类
        /// </summary>

        #region 字段
        //连接字符串
        static string strCon = "Data Source=.;Initial Catalog=Blogs;Integrated Security=True";
        //连接数据库
        SqlConnection conn = new SqlConnection(strCon);
        //创建SqlCommand类的对象comm
        SqlCommand comm = null;
        //创建MySqlDataReader类的对象reader
        public SqlDataReader reader = null;
        //创建MySqlDataAdapter类的对象adapter
        public SqlDataAdapter adapter = null;
        #endregion
        #region 打开数据库
        /// <summary>
        /// 打开数据库
        /// </summary>
        public void OpenConnection()
        {
           
            if (conn.State == ConnectionState.Broken)
            {
                conn.Close();
                conn.Open();
            }
            else if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
        }
        #endregion
        #region 关闭数据库
        /// <summary>
        /// 关闭数据库
        /// </summary>
        public void CloseConnection()
        {
            if (conn.State == ConnectionState.Broken)
            {
                conn.Close();
            }
            else if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        #endregion
        #region 增删改
        /// <summary>
        /// 增删改
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>受影响的行数</returns>
        public int IDU(string sql)
        {
            int result = -1;
            try
            {
                this.OpenConnection();
                comm = new SqlCommand(sql, conn);
                result = comm.ExecuteNonQuery();
            }
            catch (Exception)
            {

            }
            finally
            {
                this.CloseConnection();
            }
            return result;
        }
        #endregion
        #region 查询单行单列
        /// <summary>
        /// 查询单行单列
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>object类</returns>
        public object GetScalar(string sql)
        {
            object oj = new object();
            try
            {
                this.OpenConnection();
                comm = new SqlCommand(sql, conn);
                oj = comm.ExecuteScalar();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                this.CloseConnection();
            }
            return oj;
        }
        #endregion
        #region 连接式查询
        /// <summary>
        /// 连接式查询
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>MySqlDataReader类</returns>
        public SqlDataReader GetList(string sql)
        {
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    reader.Close();
                }
                this.OpenConnection();
                comm = new SqlCommand(sql, conn);
                reader = comm.ExecuteReader();
            }
            catch (Exception)
            {
                throw;
            }
            return reader;
        }
        #endregion
        #region 断开式查询
        /// <summary>
        /// 断开式查询
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>MySqlDataAdapter类</returns>
        public SqlDataAdapter GetData(string sql)
        {
            try
            {
                adapter = new SqlDataAdapter(sql, conn);
            }
            catch (Exception)
            {
                throw;
            }
            return adapter;
        }
        #endregion
    }
}
